﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace TeachingManageSystem.DAL
{
    public class DBHelper
    {
// ReSharper disable InconsistentNaming
        public static SqlConnection _conn;
// ReSharper restore InconsistentNaming
        #region 创建sqlconnection 连接对象 Conn
        public static SqlConnection Conn
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
                if (_conn == null)
                {
                    _conn = new SqlConnection(connectionString);
                    _conn.Open();
                }
                else if (_conn.State == ConnectionState.Closed)
                {
                    _conn = new SqlConnection(connectionString);
                    _conn.Open();
                }
                else if (_conn.State == ConnectionState.Broken)
                {
                    _conn.Close();
                    _conn.Open();
                }
                return _conn;
            }
        }
        #endregion
        public static SqlDataReader GetReader(string sql)
        {
            var cmd = new SqlCommand(sql, Conn);
            return cmd.ExecuteReader();
        }
        public static DataTable GetDataSet(string sql)
        {
            var cmd = new SqlCommand(sql, Conn);
            var data = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            data.Fill(ds);
            return ds.Tables[0];

        }


        /// <summary>   
        /// 向数据库里插入图像格式的字段
        /// </summary>   
        /// <param name="strSQL">SQL语句</param>   
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>   
        /// <returns>影响的记录数</returns>   
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (Conn)
            {
                var cmd = new SqlCommand(strSQL, Conn);
                var myParameter = new SqlParameter("@fs", SqlDbType.Binary) {Value = fs};
                cmd.Parameters.Add(myParameter);
                try
                {
                    Conn.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    cmd.Dispose();
                    Conn.Close();
                }
            }
        }

        /// <summary>   
        /// 执行SQL语句，返回影响的记录数   
        /// </summary>   
        /// <param name="sqlString">SQL语句</param>   
        /// <returns>影响的记录数</returns>   
        public static int ExecuteSql(string sqlString, params SqlParameter[] cmdParms)
        {
            using (Conn)
            {
                using (var cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, Conn, null, sqlString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;   
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        
        /// <summary>
        /// 处理事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public static int GetExsuce(string sql, params SqlParameter[] para)
        {
            const int i = 0;
            var tran = Conn.BeginTransaction();
            try
            {
                var cmd = new SqlCommand(sql, Conn) {Transaction = tran};
                if (para != null)
                {
                    cmd.Parameters.AddRange(para);
                }
                tran.Commit();
            }
            catch (Exception)
            {
                tran.Rollback();
                throw;
            }
            return i;
        }
        /// <summary>
        /// 对数据进行管理(插入，删除和修改)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ManageData(string sql)
        {
            var cmd = new SqlCommand(sql, Conn);
            var i = cmd.ExecuteNonQuery();
            return i;
        }

        /// <summary>
        /// 公有方法，根据Sql语句，插入记录并返回生成的ID号(可以是特定列)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetIDInsert(string sql)
        {
            var cmd = new SqlCommand(sql, Conn);
            int count = int.Parse(cmd.ExecuteScalar().ToString().Trim());
            return count;
        }

        //可以实现级联删除
        #region   批量执行SQL语句
        public static int ExecuteSqls(string[] strSqLs)
        {
            var myCmd = new SqlCommand();
            SqlTransaction myTrans = _conn.BeginTransaction();
            try
            {
                myCmd.Connection = _conn;
                myCmd.Transaction = myTrans;
                foreach (string str in strSqLs)
                {
                    myCmd.CommandText = str;
                    myCmd.ExecuteNonQuery();
                }
                myTrans.Commit();
                return 0; //表示删除成功
            }
            catch (SqlException e)
            {
                myTrans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                myCmd.Dispose();
                _conn.Close();
            }
        }
        #endregion
        //使用存储过程实现对表内容的读取。
        //若无参数则prams=null；若有参，数则要调用下面的MakeInParam方法添加参数。
        public static DataSet ProGetData(string procName, SqlParameter[] prams)
        {
            var myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
            myconn.Open();

            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, Connection = myconn, CommandText = procName };
            //按照存储过程解决
            //存储过程名
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter != null)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            var data = new SqlDataAdapter(cmd);
            var ds = new DataSet();
            data.Fill(ds);
            myconn.Close();
            return ds;
        }
        /// <summary>
        /// 只度
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">传入的参数</param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string procName, SqlParameter[] prams)
        {        
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, Connection = Conn, CommandText = procName };
            //按照存储过程解决
            //存储过程名
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter != null)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
           return  cmd.ExecuteReader();
        }



        /// <summary>
        /// 使用存储过程实现对表的增删改
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="prams"></param>
        /// <returns></returns>
        #region   使用存储过程实现对表的增删改
        public static int ProManageData(string procName, SqlParameter[] prams)
        {
            var cmd = new SqlCommand { CommandType = CommandType.StoredProcedure, Connection = Conn, CommandText = procName };
            //按照存储过程解决
            //存储过程名
            if (prams != null)
            {
                foreach (SqlParameter parameter in prams)
                {
                    if (parameter != null)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
            }
            var i = cmd.ExecuteNonQuery();
            return i;
        }

        

        public static SqlParameter MakeInParam(string paramName, SqlDbType dbType, int size, object value)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Input, value);
        }

        /// <summary>
        ///实例化一个用于调用存储过程的参数
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="direction">传递方向</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        public static SqlParameter MakeParam(string paramName, SqlDbType dbType, Int32 size, ParameterDirection direction, object value)
        {
            var param = size > 0 ? new SqlParameter(paramName, dbType, size) : new SqlParameter(paramName, dbType);

            param.Direction = direction;

            if (value != null)
            {
                param.Value = value;
            }
            return param;
        }
        #endregion
    }
}
